﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Linq;

using ECCIT;
using ECCIT.Data;
using ECCIT.Business.Model;
using ECCIT.Business.DAL;

using OgilvyOne.Model;
using OgilvyOne.SqlServerDAL;
using OgilvyOne.UKNow.Globalization;
using OgilvyOne.UKNow.Utility;
using OgilvyOne.UKNow.Model;
using OgilvyOne.UKNow.IDAL;

namespace OgilvyOne.UKNow.DAL
{
    public class EventsDAL : UKNowBaseDAL, IDALEvents
    {
        private const string TBL_NAME                   = "UKNOW_EVENTS";
        private const string COL_LANG_ID                = "COL_LANG_ID";
        private const string COL_EVENTS_ID              = "COL_EVENTS_ID";
        private const string COL_EVENTS_STATE           = "COL_EVENTS_STATE";
        private const string COL_EVENTS_TITLE           = "COL_EVENTS_TITLE";
        private const string COL_EVENTS_SUBTITLE = "COL_EVENTS_SUBTITLE";
        private const string COL_EVENTS_SPONSOR         = "COL_EVENTS_SPONSOR";
        private const string COL_EVENTS_FORM            = "COL_EVENTS_FORM";
        private const string COL_EVENTS_CITY            = "COL_EVENTS_CITY";
        private const string COL_EVENTS_VENUE           = "COL_EVENTS_VENUE";
        private const string COL_EVENTS_TIME_START = "COL_EVENTS_TIME_START";
        private const string COL_EVENTS_TIME_END = "COL_EVENTS_TIME_END";
        private const string COL_EVENTS_DESC            = "COL_EVENTS_DESC";
        private const string COL_EVENTS_DETAIL          = "COL_EVENTS_DETAIL";
        private const string COL_EVENTS_IS_HOT          = "COL_EVENTS_HOT";
        private const string COL_EVENTS_IS_RECOMMEND    = "COL_EVENTS_RECO";
        private const string COL_EVENTS_ORDER           = "COL_EVENTS_ORDER";
        private const string COL_EVENTS_HP_TOP          = "COL_EVENTS_HP_TOP";
        private const string COL_EVENTS_COL_TOP         = "COL_EVENTS_COL_TOP";
        private const string COL_EVENTS_COLUMN_ID       = "COL_EVENTS_COLUMN_ID";
        private const string COL_EVENTS_LAYER_ID        = "COL_EVENTS_LAYER_ID";
        private const string COL_EVENTS_TICKET_URL = "COL_EVENTS_TICKET_URL";
        private const string COL_EVENTS_SERIES_MAIN = "COL_EVENTS_SERIES_MAIN";
        private const string COL_EVENTS_SERIES_ID = "COL_EVENTS_SERIES_ID";
        private const string COL_EVENTS_VENUE_IMG = "COL_EVENTS_VENUE_IMG";
        private const string COL_EVENTS_INSERT_TIME     = "COL_EVENTS_INSERT_TIME";
        private const string COL_EVENTS_UPDATE_TIME     = "COL_EVENTS_UPDATE_TIME";

        private const string COL_EVENTS_SERIES_ISSPECIAL = "COL_EVENTS_SERIES_ISSPECIAL";



        /*修改人：陈鹏
         * 修改时间：2012-01-01
         * 修改内容：
         *      新增加字段 活动列表图 COL_EVENTS_LB_IMG
         *      有cp注释的为本次修改的代码
         */
        private const string COL_EVENTS_LB_IMG = "COL_EVENTS_LB_IMG"; //cp

         /*修改人：陈鹏
         * 修改时间：2012-01-01
         * 修改内容：
         *      新增加字段 活动列表图 COL_EVENTS_LB_IMG
         *      有cp注释的为本次修改的代码
         */
        private const string COL_EVENTS_TOP_IMG = "COL_EVENTS_TOP_IMG"; //cp
        /*修改人：陈鹏
        * 修改时间：2012-01-10
        * 修改内容：
        *      新增加字段 活动列模版id COL_EVENTS_TEMPLATEID
        *      有cp注释的为本次修改的代码
        */
        private const string COL_EVENTS_TEMPLATEID = "COL_EVENTS_TEMPLATEID"; //cp


        public override string TableName { get { return TBL_NAME; } }

        public override Type ModelType { get { return typeof(EventsModel); } }

        [PrimaryKey]
        public IField LanguageID { get { return FieldFactory.Create(ModelType, "LanguageID", ColumnFactory.CreateColumn(COL_LANG_ID, DataType.Int)); } }
        [PrimaryKey]
        public IField ID { get { return FieldFactory.Create(ModelType, "ID", ColumnFactory.CreateColumn(COL_EVENTS_ID, DataType.Int)); } }
        public IField State { get { return FieldFactory.Create(ModelType, "State", ColumnFactory.CreateColumn(COL_EVENTS_STATE, DataType.Int)); } }
        public IField Title { get { return FieldFactory.Create(ModelType, "Title", ColumnFactory.CreateColumn(COL_EVENTS_TITLE, DataType.String, 200)); } }
        public IField SponsorID { get { return FieldFactory.Create(ModelType, "SponsorID", ColumnFactory.CreateColumn(COL_EVENTS_SPONSOR, DataType.Int)); } }
        public IField ArtFormID { get { return FieldFactory.Create(ModelType, "ArtFormID", ColumnFactory.CreateColumn(COL_EVENTS_FORM, DataType.Int)); } }
        public IField City { get { return FieldFactory.Create(ModelType, "City", ColumnFactory.CreateColumn(COL_EVENTS_CITY, DataType.Int)); } }
        public IField Venue { get { return FieldFactory.Create(ModelType, "Venue", ColumnFactory.CreateColumn(COL_EVENTS_VENUE, DataType.String, 500)); } }
        public IField StartTime { get { return FieldFactory.Create(ModelType, "StartTime", ColumnFactory.CreateColumn(COL_EVENTS_TIME_START, DataType.DateTime)); } }
        public IField EndTime { get { return FieldFactory.Create(ModelType, "EndTime", ColumnFactory.CreateColumn(COL_EVENTS_TIME_END, DataType.DateTime)); } }

        public IField ShortDescription { get { return FieldFactory.Create(ModelType, "ShortDescription", ColumnFactory.CreateColumn(COL_EVENTS_DESC, DataType.String, 800)); } }
        public IField Detail { get { return FieldFactory.Create(ModelType, "Detail", ColumnFactory.CreateColumn(COL_EVENTS_DETAIL, DataType.NText)); } }
        public IField IsHot { get { return FieldFactory.Create(ModelType, "IsHot", ColumnFactory.CreateColumn(COL_EVENTS_IS_HOT, DataType.Bit)); } }
        public IField IsRecommendation { get { return FieldFactory.Create(ModelType, "IsRecommendation", ColumnFactory.CreateColumn(COL_EVENTS_IS_RECOMMEND, DataType.Bit)); } }
        public IField Order { get { return FieldFactory.Create(ModelType, "Order", ColumnFactory.CreateColumn(COL_EVENTS_ORDER, DataType.Int)); } }
        public IField IsHomepageTop { get { return FieldFactory.Create(ModelType, "IsHomepageTop", ColumnFactory.CreateColumn(COL_EVENTS_HP_TOP, DataType.Bit)); } }
        public IField IsColumnTop { get { return FieldFactory.Create(ModelType, "IsColumnTop", ColumnFactory.CreateColumn(COL_EVENTS_COL_TOP, DataType.Bit)); } }
        public IField ColumnID { get { return FieldFactory.Create(ModelType, "ColumnID", ColumnFactory.CreateColumn(COL_EVENTS_COLUMN_ID, DataType.Int)); } }
        public IField LayerIDs { get { return FieldFactory.Create(ModelType, "LayerIDs", ColumnFactory.CreateColumn(COL_EVENTS_LAYER_ID, DataType.String, 100)); } }

        public IField InsertedTime { get { return FieldFactory.Create(ModelType, "InsertedTime", ColumnFactory.CreateColumn(COL_EVENTS_INSERT_TIME, DataType.DateTime)); } }
        public IField UpdatedTime { get { return FieldFactory.Create(ModelType, "UpdatedTime", ColumnFactory.CreateColumn(COL_EVENTS_UPDATE_TIME, DataType.DateTime)); } }

        public IField ListImg { get { return FieldFactory.Create(ModelType, "ListImg", ColumnFactory.CreateColumn(COL_EVENTS_LB_IMG, DataType.String, 200)); } } //cp
        
        public IField TopImg { get { return FieldFactory.Create(ModelType, "TopImg", ColumnFactory.CreateColumn(COL_EVENTS_TOP_IMG, DataType.String, 200)); } } //cp

        public IField TemplateID { get { return FieldFactory.Create(ModelType, "TemplateID", ColumnFactory.CreateColumn(COL_EVENTS_TEMPLATEID, DataType.Int)); } }//cp

        public IField SubTitle { get { return FieldFactory.Create(ModelType, "SubTitle", ColumnFactory.CreateColumn(COL_EVENTS_SUBTITLE, DataType.String, 500)); } }
        public IField TicketUrl { get { return FieldFactory.Create(ModelType, "TicketUrl", ColumnFactory.CreateColumn(COL_EVENTS_TICKET_URL, DataType.String, 200)); } }
        public IField VenueImagePath { get { return FieldFactory.Create(ModelType, "VenueImagePath", ColumnFactory.CreateColumn(COL_EVENTS_VENUE_IMG, DataType.String, 200)); } }
        public IField IsSeriesMain { get { return FieldFactory.Create(ModelType, "IsSeriesMain", ColumnFactory.CreateColumn(COL_EVENTS_SERIES_MAIN, DataType.Bit)); } }
        public IField SeriesMainEventsID { get { return FieldFactory.Create(ModelType, "SeriesMainEventsID", ColumnFactory.CreateColumn(COL_EVENTS_SERIES_ID, DataType.Int)); } }
        public IField IsSpecial { get { return FieldFactory.Create(ModelType, "IsSpecial", ColumnFactory.CreateColumn(COL_EVENTS_SERIES_ISSPECIAL, DataType.Int)); } }
        public override IField[] InsertFields { get { return new IField[] { LanguageID, ID, State, Title, SubTitle, SponsorID, ArtFormID, City, Venue, StartTime, EndTime, ShortDescription, Detail, IsHot, IsRecommendation, Order, IsHomepageTop, IsColumnTop, ColumnID, LayerIDs, InsertedTime, UpdatedTime, ListImg, TopImg, TemplateID, TicketUrl, VenueImagePath, IsSeriesMain, SeriesMainEventsID }; } }
        public override IField[] SelectFields { get { return new IField[] { LanguageID, ID, State, Title, SubTitle, SponsorID, ArtFormID, City, Venue, StartTime, EndTime, ShortDescription, Detail, IsHot, IsRecommendation, Order, IsHomepageTop, IsColumnTop, ColumnID, LayerIDs, InsertedTime, UpdatedTime, ListImg, TopImg, TemplateID, TicketUrl, VenueImagePath, IsSeriesMain, SeriesMainEventsID,IsSpecial }; } }
        public override IField[] UpdateFields { get { return new IField[] { LanguageID, State, Title, SubTitle, SponsorID, ArtFormID, City, Venue, StartTime, EndTime, ShortDescription, Detail, IsHot, IsRecommendation, Order, IsHomepageTop, IsColumnTop, UpdatedTime, ListImg, TopImg, TemplateID, TicketUrl, VenueImagePath, IsSeriesMain, SeriesMainEventsID }; } }

        public EventsDAL() { }

        public EventsDAL(LanguageType languageType) { this.Language = LanguageFactory.Create(languageType); }

        private void SetForeignKeyModel(EventsModel model)
        {
            if (model == null) return;
            model.Sponsor = new SponsorDAL(this.Language.LanguageType).GetSponsorModelByID(model.SponsorID);
            model.ArtForm = new ArtFormDAL(this.Language.LanguageType).GetArtFormModelByID(model.ArtFormID);
        }

        #region IDALEvents Members

        public EventsModel GetEventsModelByID(int? id)
        {
            EventsModel model = (EventsModel)GetModelByPrimaryKey(this.GetType(), new KeyValuePair<IField, object>[] { new KeyValuePair<IField, object>(LanguageID, (int)this.Language.LanguageType), new KeyValuePair<IField, object>(this.ID, id) });
            SetForeignKeyModel(model);
            return model;
        }

        public IList<EventsModel> GetAllEventsModels()
        {
            string whereClause = string.Format("where {0}={1}", COL_LANG_ID, (int)this.Language.LanguageType);
            /*修改人：穆凤凤
        * 修改时间：2012-02-28
        * 修改内容：
        *      修改参数 活动列表 按id升序排列
        *      有mff注释的为本次修改的代码
        */
            //mff
            IList<EventsModel> models = GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(ID.PropertyName, SortOrder.Asc))).List.Cast<EventsModel>().ToList();
            foreach (EventsModel model in models)
                SetForeignKeyModel(model);

            return models;
        }
        //获得特色活动列表
        public IList<EventsModel> GetAllEventsModels(int isSpecial)
        {
            string whereClause = string.Format("where {0}={1} and {2}={3}", COL_LANG_ID, (int)this.Language.LanguageType, COL_EVENTS_SERIES_ISSPECIAL, isSpecial);
       
            IList<EventsModel> models = GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(ID.PropertyName, SortOrder.Asc))).List.Cast<EventsModel>().ToList();
            foreach (EventsModel model in models)
                SetForeignKeyModel(model);

            return models;
        }
        //根据具体查找条件,获得活动的记录
        //mff
        public IList<EventsModel> GetEventsModelByName(string ename,int? state)
        {
            StringBuilder stb = new StringBuilder();
            stb.AppendFormat("where COL_LANG_ID={0} and COL_EVENTS_TITLE like '%{1}%' ",((int)this.Language.LanguageType).ToString(), ename);
            if (state == 0)
            {
                stb.AppendFormat("and  COL_EVENTS_STATE={0}", state);
            }
            else
            {
                if (state == 1)
                {
                    stb.AppendFormat("and  COL_EVENTS_STATE={0}", state);
                }
                else
                { stb.Append("and  COL_EVENTS_STATE in (0,1)"); }

            }
            string whereClause = stb.ToString();
            IList<EventsModel> models = GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(ID.PropertyName, SortOrder.Asc))).List.Cast<EventsModel>().ToList();
            foreach (EventsModel model in models)
                SetForeignKeyModel(model);

            return models;
        }
        //mff
        /*修改人：穆凤凤
        * 修改时间：2012-03-08
        * 修改内容：
        *      新增方法 获得按活动语言查询出的实体 GetEventModelsByName,用于图片和视频的同步
        *      有mff注释的为本次修改的代码
        */
        //mff
        public List<EventsModel> GetEventModelsByName(int langid)
        {
            string whereClause = "select distinct col_events_title from uknow_events where col_lang_id=" + langid + "order by col_events_title";

              DataSet ds=DBHelper.ExecuteDataSet(whereClause,null);
              DataTable dt=ds.Tables[0];
              List<EventsModel> iLevents = new List<EventsModel>();
              EventsModel em = null;
              if (dt.Rows.Count>0)
              {
                  foreach (DataRow dr in dt.Rows)
                  {
                      em = new EventsModel();
                      em.Title = dr["col_events_title"].ToString();
                      iLevents.Add(em);
                  }
              }
              return iLevents;
            
        }
        public IList<EventsModel> GetEveByNandL(int langid, string eventName)
        {
            string whereClause = string.Format("where {0}={1} and {2} like '%{3}%'", COL_LANG_ID, langid, COL_EVENTS_TITLE, eventName.Replace("'", "''"));
            IList<EventsModel> models = GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(ID.PropertyName, SortOrder.Asc))).List.Cast<EventsModel>().ToList();
            foreach (EventsModel model in models)
                SetForeignKeyModel(model);

            return models;
        }
        //mff
        public IList<EventsModel> GetPublishedEventsModels()
        {
            WhereClauseBuilder builder = WhereClauseBuilder.CreateInstance(SearchConditionJoinOperator.AND);
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_EVENTS_STATE, 1, SearchConditionOperator.Equals));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_LANG_ID, (int)this.Language.LanguageType, SearchConditionOperator.Equals));

            string whereClause = builder.ToSqlCommand();
            IList<EventsModel> models = GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(UpdatedTime.PropertyName, SortOrder.Desc))).List.Cast<EventsModel>().ToList();
            foreach (EventsModel model in models)
                SetForeignKeyModel(model);

            return models;
        }

        public PagingList GetCalendarDefaultEvents(int? cityID, PagingParameter pagingParams)
        {
            IField[] fields = new IField[] { LanguageID, ID, State, Title, SponsorID, ArtFormID, City, Venue, StartTime, EndTime, ShortDescription, IsHot, IsRecommendation, Order, IsHomepageTop, IsColumnTop, ColumnID, LayerIDs, InsertedTime, UpdatedTime, ListImg, TopImg, TemplateID, TicketUrl, VenueImagePath };
            StringBuilder builder = new StringBuilder();

            if (cityID.HasValue)
            {
                string cmdSel1 = string.Format( "select {4} from {0} where {1}={2} and {3}>getdate() and {5}={6} order by {2} asc", COL_EVENTS_TIME_END, COL_EVENTS_TIME_START, "EventsEndTime", TBL_NAME, COL_EVENTS_ID, COL_EVENTS_CITY, cityID.Value);

            // 没有结束的活动按照开始时间正排序
            string cmdSel2 = string.Format("select {4}, isnull({0}, {1}) as {2} from {3} where isnull({0},{1})>=getdate() and {5}={6} order by {2} asc", COL_EVENTS_TIME_END, COL_EVENTS_TIME_START, "EventsEndTime", TBL_NAME, COL_EVENTS_ID, COL_EVENTS_CITY, cityID.Value);

            // 已经结束的活动按照结束时间倒排序
            string cmdSel3 = string.Format("select {4}, isnull({0},{1}) as {2} from {3} where isnull({0},{1})<getdate() and {5}={6} order by {2} asc", COL_EVENTS_TIME_END, COL_EVENTS_TIME_START, "EventsEndTime", TBL_NAME, COL_EVENTS_ID, COL_EVENTS_CITY, cityID.Value);
            builder.Append(cmdSel1).Append(cmdSel2).Append(cmdSel3);
            }

            string cmdSel4 = string.Format( "select {4} from {0} where {1}={2} and {3}>getdate() order by {2} asc", COL_EVENTS_TIME_END, COL_EVENTS_TIME_START, "EventsEndTime", TBL_NAME, COL_EVENTS_ID);

            // 没有结束的活动按照开始时间正排序
            string cmdSel5 = string.Format("select {4}, isnull({0}, {1}) as {2} from {3} where isnull({0},{1})>=getdate() order by {2} asc", COL_EVENTS_TIME_END, COL_EVENTS_TIME_START, "EventsEndTime", TBL_NAME, COL_EVENTS_ID);

            // 已经结束的活动按照结束时间倒排序
            string cmdSel6 = string.Format("select {4}, isnull({0},{1}) as {2} from {3} where isnull({0},{1})<getdate() order by {2} asc", COL_EVENTS_TIME_END, COL_EVENTS_TIME_START, "EventsEndTime", TBL_NAME, COL_EVENTS_ID);

            string whereClause = string.Format("where {0} in ({1}) and {2}={3}", COL_EVENTS_ID, builder.ToString(), COL_LANG_ID, (int)this.Language.LanguageType);
            PagingList models = GetModels(whereClause, pagingParams);
            return models;
        }

        /// <summary>
        ///     获取系列活动主活动数据
        /// </summary>
        /// <returns></returns>
        public IList<EventsModel> GetSeriesMainEvents()
        {
            string whereClause = string.Format("where {0}={1} and {2}={3}", COL_EVENTS_SERIES_MAIN, 1, COL_LANG_ID, (int)this.Language.LanguageType);
            return GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(UpdatedTime.PropertyName, SortOrder.Desc))).List.Cast<EventsModel>().ToList();
        }

        /// <summary>
        ///     获取活动ID为eventsID的活动的相关活动数据
        /// </summary>
        /// <param name="eventsID"></param>
        /// <returns></returns>
        public IList<EventsModel> GetEventsModelsByEventsIDs(IList<int?> ids)
        {
            WhereClauseBuilder builder = WhereClauseBuilder.CreateInstance(SearchConditionJoinOperator.AND);
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_LANG_ID, (int)this.Language.LanguageType, SearchConditionOperator.Equals));
            string whereClause = builder.ToSqlCommand();
            if (ids.Count > 0)
            {
                whereClause += " and " + COL_EVENTS_ID + " in (";
                int i = 0;
                foreach (int? id in ids)
                {
                    if (i > 0)
                        whereClause += ",";
                    whereClause += id;
                    i++;
                }
                whereClause += ")";
            }

            return GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateNonSortingParameter())).List.Cast<EventsModel>().ToList();
        }

        /// <summary>
        ///     获取活动ID为eventsID的活动的系列活动数据
        /// </summary>
        /// <param name="eventsID"></param>
        /// <returns></returns>
        public IList<EventsModel> GetSeriesEvents(int? eventsID, bool includeSelf)
        {
            IList<EventsModel> events = new List<EventsModel>();
            EventsModel model = GetEventsModelByID(eventsID);
            if (model == null) return events;
            if (!model.SeriesMainEventsID.HasValue) return events;

            WhereClauseBuilder builder = WhereClauseBuilder.CreateInstance(SearchConditionJoinOperator.AND);
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_LANG_ID, (int)this.Language.LanguageType, SearchConditionOperator.Equals));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_EVENTS_SERIES_ID, model.SeriesMainEventsID, SearchConditionOperator.Equals));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_EVENTS_ID, eventsID, SearchConditionOperator.NotEquals));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_EVENTS_STATE, 1, SearchConditionOperator.Equals));

            string whereClause = builder.ToSqlCommand();
            events = GetModels(whereClause, null).List.Cast<EventsModel>().ToList();
            return events;
        }

        /// <summary>
        ///     
        /// </summary>
        /// <param name="yearMonth">形如：2012-02的年月格式</param>
        /// <returns></returns>
        public IList<DictionaryManageModel> GetEventsCitiesByYearMonth(string yearMonth)
        {
            string sql = "select distinct {0}, count({1}) as eventscount from {2} where " + COL_EVENTS_STATE + "=1 and " + COL_LANG_ID + "=" + (int)this.Language.LanguageType;
            string cmd = null;
            if (!string.IsNullOrEmpty(yearMonth) && yearMonth != "null" && yearMonth != "0" && yearMonth.Length == 7)
            {
                // 获取活动举办时间在startDate和endDate间活动的方法
                // 活动开始时间<startDate and 活动结束时间>startDate
                // 活动开始时间>startDate and 活动开始时间<endDate
                string startDate = yearMonth + "-01";
                string endDate = DateTimeUtility.GetEndDateOfMonth(yearMonth);
                sql += " and ((DATEDIFF(dd, {3}, '{4}') >=0  and DATEDIFF(dd, {5}, '{4}') <=0) or (DATEDIFF(dd, {3}, '{4}') <=0  and DATEDIFF(dd, {3}, '{6}') >=0)) " +
                    "group by {0} order by eventscount desc";
                cmd = string.Format(sql, COL_EVENTS_CITY, COL_EVENTS_ID, TBL_NAME, COL_EVENTS_TIME_START, startDate, COL_EVENTS_TIME_END, endDate);
            }
            else
            {
                sql += " group by {0} order by eventscount desc";
                cmd = string.Format(sql, COL_EVENTS_CITY, COL_EVENTS_ID, TBL_NAME);
            }

            List<int> cityIDs = new List<int>();
            using (IDataReader reader = DBHelper.ExecuteReader(cmd))
            {
                while (reader.Read())
                {
                    if (reader[COL_EVENTS_CITY] != DBNull.Value)
                        cityIDs.Add((int)reader[COL_EVENTS_CITY]);
                }
            }
            
            StringBuilder builder = new StringBuilder(" cid in (");
            int i = 0;
            foreach (int cityID in cityIDs)
            {
                if (i > 0)
                    builder.Append(",");
                builder.Append(cityID);
                i++;
            }
            if (i == 0)
                builder.Append("''");
            builder.Append(")");

            DictionaryManage dm = new DictionaryManage();
            DataSet dataSet = dm.GetDictionary(builder.ToString());
            if (dataSet.Tables.Count == 0 || dataSet.Tables[0].Rows.Count == 0) return null;

            IList<DictionaryManageModel> list = new List<DictionaryManageModel>();
            foreach (DataRow dr in dataSet.Tables[0].Rows)
            {
                DictionaryManageModel model = new DictionaryManageModel();
                model.Cid = (int)dr["cid"];
                model.Cnname = (string)dr["cnname"];
                model.Enname = (string)dr["enname"];
                list.Add(model);
            }

            return list;
        }

        public IList<int> GetEventsDayInPeriod(string startDate, string endDate)
        {
            // 两种情况满足条件：
            // 1. col_events_time_start <= startDate and col_events_time_end >= startDate
            // 2. col_events_time_start >= startDate and col_events_time_start <= endDate
            string cmd = string.Format("select {0}, {3}, datediff(dd, {0}, {3}) as d_diff from {1} where ((datediff(dd, {0}, '{2}')>=0 and datediff(dd, {3}, '{2}')<=0) or (datediff(dd, {0}, '{2}')<=0 and datediff(dd, {0}, '{4}')>=0)) and {5}={6} and {7}=1 and {0} is not null",
                COL_EVENTS_TIME_START, TBL_NAME, startDate, COL_EVENTS_TIME_END, endDate, COL_LANG_ID, (int)this.Language.LanguageType, COL_EVENTS_STATE);

            IList<int> list = new List<int>();
            using (IDataReader reader = DBHelper.ExecuteReader(cmd))
            {
                DateTime? startTime, endTime;
                int dateDiff = 0;
                while (reader.Read())
                {
                    startTime = (DateTime?)reader[COL_EVENTS_TIME_START];
                    endTime = (reader[COL_EVENTS_TIME_END] == DBNull.Value) ? null : (DateTime?)reader[COL_EVENTS_TIME_END];
                    if (endTime.HasValue) dateDiff = (int)reader["d_diff"];

                    if (!list.Contains(startTime.Value.Day)) list.Add(startTime.Value.Day);

                    while (dateDiff > 0)
                    {
                        if (startTime.Value.AddDays(dateDiff) <= DateTime.Parse(endDate) && startTime.Value.AddDays(dateDiff) >= DateTime.Parse(startDate))
                            if (!list.Contains(startTime.Value.AddDays(dateDiff).Day)) list.Add(startTime.Value.AddDays(dateDiff).Day);
                        dateDiff--;
                    }
                }
            }

            return list;
        }

        public IList<string> GetAllPublishedEventsYearMonth()
        {
            string cmd = string.Format("select {0}, {1}, datediff(month, {0}, {1}) as m_diff from {2} where {0} is not null and {3}=1 and {4}={5} order by {0}", 
                COL_EVENTS_TIME_START, COL_EVENTS_TIME_END, TBL_NAME, COL_EVENTS_STATE, COL_LANG_ID, (int)this.Language.LanguageType);

            IList<string> list = new List<string>();
            using (IDataReader reader = DBHelper.ExecuteReader(cmd))
            {
                DateTime? startTime, endTime;
                string startYM, endYM;
                int monthDiff = 0;
                while (reader.Read())
                {
                    startTime = (DateTime?)reader[COL_EVENTS_TIME_START];
                    endTime = (reader[COL_EVENTS_TIME_END] == null || reader[COL_EVENTS_TIME_END] == DBNull.Value) ? null : (DateTime?)reader[COL_EVENTS_TIME_START];
                    if (endTime.HasValue) monthDiff = (int)reader["m_diff"];

                    // 首先向list里增加开始月份
                    startYM = FormatYearMonth(startTime);
                    if (!list.Contains(startYM)) list.Add(startYM);

                    while (monthDiff > 0)
                    {
                        string s = FormatYearMonth(startTime.Value.AddMonths(monthDiff));
                        if (!list.Contains(s)) list.Add(s);
                        monthDiff--;
                    }
                }
            }

            List<string> sl = list.ToList<string>();
            sl.Sort();
            return sl;
        }

        public IList<EventsModel> GetRelatedEvents(int? eventsID, PagingParameter pagingParams)
        {
            IList<EventsModel> models = new List<EventsModel>();
            EventsModel currEventInfo = GetEventsModelByID(eventsID);
            if (currEventInfo == null) return models;

            if (pagingParams == null)
                pagingParams = PagingParameterFactory.CreateInstance(true, 0, 3, SortingParameterFactory.CreateInstance(StartTime.PropertyName, SortOrder.Asc));

            List<int> ids = new List<int>();
            string timeWhereClause = " 1=1 ";
            //if (currEventInfo.StartTime != null && currEventInfo.EndTime != null)
            //{
            //    timeWhereClause = string.Format(" ((DATEDIFF(dd, {0}, '{1}') >=0  and DATEDIFF(dd, {2}, '{1}') <=0) or (DATEDIFF(dd, {0}, '{1}') <=0  and DATEDIFF(dd, {0}, '{3}') >=0)) ",
            //        COL_EVENTS_TIME_START, currEventInfo.StartTime.Value.ToString("yyyy-MM-dd"), COL_EVENTS_TIME_END, currEventInfo.EndTime.Value.ToString("yyyy-MM-dd"));
            //}
            //else if (currEventInfo.StartTime != null)
            //{
            //    timeWhereClause = string.Format(" ((col_events_time_end is null and datediff(dd, col_events_time_start, '{0}')=0) " +
            //                        "or (" +
            //                            "col_events_time_end is not null " +
            //                            "and datediff(dd, col_events_time_end, '{0}')<=0 " +
            //                            "and datediff(dd, col_events_time_start, '{0}')>=0 " +
            //                        "))", currEventInfo.StartTime.Value.ToString("yyyy-MM-dd"));
            //}

            // 首先找出具有相关性的活动
            string whereClause = string.Format(" where {0}={1} and {2}={3} and {4}<>{5} and col_events_time_start is not null and {6}='{7}' and {8}='{9}' and {10}", 
                COL_LANG_ID, (int)this.Language.LanguageType, 
                COL_EVENTS_STATE, 1,
                COL_EVENTS_ID, eventsID,
                COL_EVENTS_FORM, (currEventInfo.ArtFormID.HasValue) ? currEventInfo.ArtFormID.Value : -1,
                COL_EVENTS_CITY, (currEventInfo.City.HasValue) ? currEventInfo.City.Value : -1, timeWhereClause);

            ReturnEventsModelsByWhereClause(whereClause, pagingParams, models, ids);

            if (models.Count < pagingParams.PageSize)
            {
                whereClause = string.Format(" where {0}={1} and {2}={3} and {4}<>{5} and col_events_time_start is not null and {6}='{7}' and {8} ",
                    COL_LANG_ID, (int)this.Language.LanguageType,
                    COL_EVENTS_STATE, 1,
                    COL_EVENTS_ID, eventsID,
                    COL_EVENTS_CITY, (currEventInfo.City.HasValue) ? currEventInfo.City.Value : -1, timeWhereClause);

                ReturnEventsModelsByWhereClause(whereClause, pagingParams, models, ids);
            }

            if (models.Count < pagingParams.PageSize)
            {
                whereClause = string.Format(" where {0}={1} and {2}={3} and {4}<>{5} and col_events_time_start is not null and {6}='{7}' and {8} ",
                    COL_EVENTS_STATE, 1,
                    COL_LANG_ID, (int)this.Language.LanguageType,
                    COL_EVENTS_ID, eventsID,
                    COL_EVENTS_FORM, (currEventInfo.ArtFormID.HasValue) ? currEventInfo.ArtFormID.Value : -1, timeWhereClause);

                ReturnEventsModelsByWhereClause(whereClause, pagingParams, models, ids);
            }

            if (models.Count < pagingParams.PageSize)
            {
                whereClause = string.Format(" where {0}={1} and {2}={3} and {4}<>{5} and col_events_time_start is not null and {6} ",
                    COL_LANG_ID, (int)this.Language.LanguageType,
                    COL_EVENTS_STATE, 1,
                    COL_EVENTS_ID, eventsID,
                    timeWhereClause);

                ReturnEventsModelsByWhereClause(whereClause, pagingParams, models, ids);
            }

            /*
             * 代码添加
             * 添加人员：刘扬
             * 添加日期：2012-03-13
             * 添加原因：如果增加了时间范围的筛选后，取不出3条相关热门活动，则去掉时间范围筛选条件
             * */
            if (models.Count < pagingParams.PageSize)
            {
                whereClause = string.Format(" where {0}={1} and {2}={3} and {4}<>{5} and {6}='{7}' and {8}='{9}' ",
                    COL_LANG_ID, (int)this.Language.LanguageType,
                    COL_EVENTS_STATE, 1,
                    COL_EVENTS_ID, eventsID,
                    COL_EVENTS_FORM, (currEventInfo.ArtFormID.HasValue) ? currEventInfo.ArtFormID.Value : -1,
                    COL_EVENTS_CITY, (currEventInfo.City.HasValue) ? currEventInfo.City.Value : -1);

                ReturnEventsModelsByWhereClause(whereClause, pagingParams, models, ids);
            }

            if (models.Count < pagingParams.PageSize)
            {
                whereClause = string.Format(" where {0}={1} and {2}={3} and {4}<>{5} and {6}='{7}' ",
                    COL_LANG_ID, (int)this.Language.LanguageType,
                    COL_EVENTS_STATE, 1,
                    COL_EVENTS_ID, eventsID,
                    COL_EVENTS_FORM, (currEventInfo.ArtFormID.HasValue) ? currEventInfo.ArtFormID.Value : -1);

                ReturnEventsModelsByWhereClause(whereClause, pagingParams, models, ids);
            }

            if (models.Count < pagingParams.PageSize)
            {
                whereClause = string.Format(" where {0}={1} and {2}={3} and {4}<>{5} and {6}='{7}' ",
                    COL_LANG_ID, (int)this.Language.LanguageType,
                    COL_EVENTS_STATE, 1,
                    COL_EVENTS_ID, eventsID,
                    COL_EVENTS_CITY, (currEventInfo.City.HasValue) ? currEventInfo.City.Value : -1);

                ReturnEventsModelsByWhereClause(whereClause, pagingParams, models, ids);
            }

            foreach (EventsModel model in models)
                SetForeignKeyModel(model);

            return models;
        }

        private void ReturnEventsModelsByWhereClause(string whereClause, PagingParameter pagingParams, IList<EventsModel> models, List<int> ids)
        {
            PagingList list = GetModels(whereClause, pagingParams);
            int count = models.Count;
            for (int i = 0; i < list.List.Count && i < pagingParams.PageSize - count; i++)
            {
                if (!ids.Contains(((EventsModel)list.List[i]).ID.Value))
                {
                    models.Add((EventsModel)list.List[i]);
                    ids.Add(((EventsModel)list.List[i]).ID.Value);
                }
            }
        }

        private string FormatYearMonth(DateTime? dateTime)
        {
            if (!dateTime.HasValue) return null;
            string startYM = dateTime.Value.Year + "-";
            if (dateTime.Value.Month < 10) startYM += "0";
            startYM += dateTime.Value.Month;
            return startYM;
        }

        public ResultInfo Save(EventsModel modelInfo)
        {
            if (modelInfo == null) throw new ArgumentNullException("modelInfo");

            ResultInfo result = new ResultInfo(false);

            try
            {
                int rowsAffected = 0;
                if (modelInfo.ID == null)   // insert
                {
                    modelInfo.ID = SqlUtil.GetMaxRecordID(TableName, COL_EVENTS_ID, COL_LANG_ID, (int)this.Language.LanguageType);
                    //modelInfo.State = 0;    // 强制将活动状态置为未发布
                    modelInfo.InsertedTime = DateTime.Now;
                    modelInfo.UpdatedTime = DateTime.Now;
                    if (modelInfo.IsSeriesMain)
                        modelInfo.SeriesMainEventsID = modelInfo.ID;
                    rowsAffected = Insert(modelInfo, null);
                }
                else // update
                {
                    modelInfo.UpdatedTime = DateTime.Now;
                    if (modelInfo.IsSeriesMain)
                        modelInfo.SeriesMainEventsID = modelInfo.ID;
                    rowsAffected = UpdateByPrimaryKey(modelInfo, null);
                }

                if (rowsAffected > 0)
                {
                    result.Message = "活动数据保存成功！";
                    result.Result = true;
                    result.ReturnObject = modelInfo;
                }
            }
            catch (Exception ex)
            {
                result.Exception = ex;
                result.Message = string.Format("保存活动数据时发生异常。异常信息：{0}", ex.Message);
            }

            return result;
        }

        public ResultInfo Delete(IList<int?> idList)
        {
            ResultInfo result = new ResultInfo(false);
            using (IDbConnection conn = DBHelper.GetDbConnection())
            {
                conn.Open();
                IDbTransaction trans = conn.BeginTransaction();
                try
                {
                    EventsCommentDAL ecd = new EventsCommentDAL(this.Language.LanguageType);
                    EventsImageDAL eid = new EventsImageDAL(this.Language.LanguageType);
                    EventsVideoDAL evd = new EventsVideoDAL(this.Language.LanguageType);
                    EventsLogDAL eld = new EventsLogDAL(this.Language.LanguageType);
                    foreach (int? id in idList)
                    {
                        /*
                         * 代码修改
                         * 修改人员：刘扬
                         * 修改日期：2012-02-14
                         * 修改描述：MD，情人节凌晨0:18分还在这里写程序，真是禽兽。
                         * */
                        ecd.DeleteByEventsID(trans, id);        // 删除活动评论
                        eid.DeleteByEventsID(trans, id);        // 删除活动图片
                        evd.DeleteByEventsID(trans, id);        // 删除活动视频
                        eld.DeleteByEventsID(trans, id);        // 删除活动浏览日志

                        // 删除活动
                        DeleteModelByPrimaryKey(trans, this.GetType(), new KeyValuePair<IField, object>[] { new KeyValuePair<IField, object>(LanguageID, (int)this.Language.LanguageType), new KeyValuePair<IField, object>(this.ID, id) });
                    }

                    trans.Commit();
                    result.Result = true;
                    result.Message = "活动数据删除成功！";
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    result.Message = string.Format("删除活动数据时发生异常。异常信息：{0}", ex.Message);
                }
                finally
                {
                    trans.Dispose();
                }
            }

            return result;
        }

        public ResultInfo Publish(IList<int?> idList)
        {
            ResultInfo result = new ResultInfo(false);

            IField[] updatedFields = new IField[] { this.State };
            IDataParameter[] parms = DBHelper.GetDataParameters(updatedFields);
            parms[0].Value = 1;

            string ids = "";
            foreach (int? id in idList)
                ids += (ids == string.Empty) ? id.ToString() : "," + id.ToString();

            WhereClauseBuilder builder = WhereClauseBuilder.CreateInstance(SearchConditionJoinOperator.AND);
            builder.Conditions.Add(InSearchCondition.CreateInstance(COL_EVENTS_ID, DataType.Int, ids));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_LANG_ID, (int)this.Language.LanguageType, SearchConditionOperator.Equals));

            string whereClause = builder.ToSqlCommand();
            string cmdText = DBHelper.GetUpdateCommandText(this.TableName, updatedFields, whereClause);

            try
            {
                DBHelper.ExecuteNonQuery(cmdText, parms);
                result.Result = true;
                result.Message = "活动发布成功！";
            }
            catch (Exception ex)
            {
                result.Message = string.Format("发布活动时发生异常。异常信息：{0}", ex.Message);
            }

            return result;
        }

        public ResultInfo UnPublish(IList<int?> idList)
        {
            ResultInfo result = new ResultInfo(false);

            IField[] updatedFields = new IField[] { this.State };
            IDataParameter[] parms = DBHelper.GetDataParameters(updatedFields);
            parms[0].Value = 0;

            string ids = "";
            foreach (int? id in idList)
                ids += (ids == string.Empty) ? id.ToString() : "," + id.ToString();

            WhereClauseBuilder builder = WhereClauseBuilder.CreateInstance(SearchConditionJoinOperator.AND);
            builder.Conditions.Add(InSearchCondition.CreateInstance(COL_EVENTS_ID, DataType.Int, ids));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_LANG_ID, (int)this.Language.LanguageType, SearchConditionOperator.Equals));

            string whereClause = builder.ToSqlCommand();
            string cmdText = DBHelper.GetUpdateCommandText(this.TableName, updatedFields, whereClause);

            try
            {
                DBHelper.ExecuteNonQuery(cmdText, parms);
                result.Result = true;
                result.Message = "活动取消发布成功！";
            }
            catch (Exception ex)
            {
                result.Message = string.Format("取消发布活动时发生异常。异常信息：{0}", ex.Message);
            }

            return result;
        }




        public ResultInfo SetIsSpecial(IList<int?> idList)
        {
            ResultInfo result = new ResultInfo(false);

            IField[] updatedFields = new IField[] { this.IsSpecial };
            IDataParameter[] parms = DBHelper.GetDataParameters(updatedFields);
            parms[0].Value = 1;

            string ids = "";
            foreach (int? id in idList)
                ids += (ids == string.Empty) ? id.ToString() : "," + id.ToString();

            WhereClauseBuilder builder = WhereClauseBuilder.CreateInstance(SearchConditionJoinOperator.AND);
            builder.Conditions.Add(InSearchCondition.CreateInstance(COL_EVENTS_ID, DataType.Int, ids));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_LANG_ID, (int)this.Language.LanguageType, SearchConditionOperator.Equals));

            string whereClause = builder.ToSqlCommand();
            string cmdText = DBHelper.GetUpdateCommandText(this.TableName, updatedFields, whereClause);

            try
            {
                DBHelper.ExecuteNonQuery(cmdText, parms);
                result.Result = true;
                result.Message = "特色活动修改成功！";
            }
            catch (Exception ex)
            {
                result.Message = string.Format("修改特色活动时发生异常。异常信息：{0}", ex.Message);
            }

            return result;
        }

        public ResultInfo SetNotSpecial(IList<int?> idList)
        {
            ResultInfo result = new ResultInfo(false);

            IField[] updatedFields = new IField[] { this.IsSpecial };
            IDataParameter[] parms = DBHelper.GetDataParameters(updatedFields);
            parms[0].Value = 0;

            string ids = "";
            foreach (int? id in idList)
                ids += (ids == string.Empty) ? id.ToString() : "," + id.ToString();

            WhereClauseBuilder builder = WhereClauseBuilder.CreateInstance(SearchConditionJoinOperator.AND);
            builder.Conditions.Add(InSearchCondition.CreateInstance(COL_EVENTS_ID, DataType.Int, ids));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_LANG_ID, (int)this.Language.LanguageType, SearchConditionOperator.Equals));

            string whereClause = builder.ToSqlCommand();
            string cmdText = DBHelper.GetUpdateCommandText(this.TableName, updatedFields, whereClause);

            try
            {
                DBHelper.ExecuteNonQuery(cmdText, parms);
                result.Result = true;
                result.Message = "非特色活动修改成功！";
            }
            catch (Exception ex)
            {
                result.Message = string.Format("非特色活动修改时生异常。异常信息：{0}", ex.Message);
            }

            return result;
        }



        public PagingList GetFilteredEventsModels(string artFormIDs, string yearMonth, string date, string cityIDs, PagingParameter pagingParams)
        {
            WhereClauseBuilder builder = WhereClauseBuilder.CreateInstance(SearchConditionJoinOperator.AND);
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_LANG_ID, (int)this.Language.LanguageType, SearchConditionOperator.Equals));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_EVENTS_STATE, 1, SearchConditionOperator.Equals));

            if (!string.IsNullOrEmpty(artFormIDs) && artFormIDs != "0")
                builder.Conditions.Add(InSearchCondition.CreateInstance(COL_EVENTS_FORM, DataType.Int, artFormIDs));
            if (!string.IsNullOrEmpty(cityIDs) && cityIDs != "0")
                builder.Conditions.Add(InSearchCondition.CreateInstance(COL_EVENTS_CITY, DataType.Int, cityIDs));

            string whereClause = (builder.Conditions.Count == 0) ? "WHERE 1=1 " : builder.ToSqlCommand();

            // 获取活动举办时间在startDate和endDate间活动的方法
            // 活动开始时间<startDate and 活动结束时间>startDate
            // 活动开始时间>startDate and 活动开始时间<endDate
            if (!string.IsNullOrEmpty(date) && date != "null" && date != "0") // date为0表示不根据日期进行筛选
            {
                //whereClause += string.Format(" and datediff(dd, {0}, '{1}') >= 0 and datediff(dd, {2}, '{1}') <= 0 ", COL_EVENTS_TIME_START, date, COL_EVENTS_TIME_END);
                whereClause += string.Format(" and ((col_events_time_end is null and datediff(dd, col_events_time_start, '{0}')=0) " +
                                "or (" +
                                    "col_events_time_end is not null " +
                                    "and datediff(dd, col_events_time_end, '{0}')<=0 " +
                                    "and datediff(dd, col_events_time_start, '{0}')>=0 " +
                                "))", date);
            }
            else
            {
                if (!string.IsNullOrEmpty(yearMonth) && yearMonth != "null" && yearMonth != "0")
                {
                    string startDate = yearMonth + "-01";
                    string endDate = DateTimeUtility.GetEndDateOfMonth(yearMonth);
                    whereClause += string.Format(" and ((DATEDIFF(dd, {0}, '{1}') >=0  and DATEDIFF(dd, {2}, '{1}') <=0) or (DATEDIFF(dd, {0}, '{1}') <=0  and DATEDIFF(dd, {0}, '{3}') >=0)) ",
                        COL_EVENTS_TIME_START, startDate, COL_EVENTS_TIME_END, endDate);
                }
            }

            pagingParams.SortingParameter = SortingParameterFactory.CreateInstance(StartTime.PropertyName, SortOrder.Asc);
            string cmdSelect = string.Format("select *, ISNULL({0}, '9999-12-31') as sorder from {1} {2} order by sorder, {0} asc", COL_EVENTS_TIME_START, TableName, whereClause);
            int totalRecordCount;
            DataSet dataSet = DBHelper.ExecuteDataSet(cmdSelect, pagingParams.CurrentPageIndex, pagingParams.PageSize, out totalRecordCount);
            PagingList list = new PagingList();
            list.List = GetModelListFromDataSet(ModelType, dataSet, SelectFields);
            list.Capacity = totalRecordCount;
            //PagingList list = GetModels(whereClause, pagingParams);

            foreach (EventsModel model in list.List)
                SetForeignKeyModel(model);

            return list;
        }
        #endregion
    }
}
